package modelosqlserver;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.SwingUtilities;

import transacciones.ConeccionDAO;
import transacciones.TransactionDelegate;
import utilitario.FuncionesUsuario;

public class ClienteDAO extends ConeccionDAO {

	private Cliente cli;
	private FuncionesUsuario funUsu;

	public ClienteDAO(Cliente parCli, TransactionDelegate parTransactionDelegate, FuncionesUsuario funUsu) {

		super(parTransactionDelegate);
        this.cli = parCli;
        this.funUsu = funUsu;
        
	}

	public int agregar(Cliente parCli) {

		int resCod = 0;
		String conSql;
		PreparedStatement pstm;
		ResultSet resSql;

		try {

			conSql = "insert into cliente values(?,?,?,?,?,?,?,?,?,?)";
			pstm = obtenerConeccion().prepareStatement(conSql, Statement.RETURN_GENERATED_KEYS);

			// pstm.setInt(1, parCli.getCli_cod());
			pstm.setString(1, parCli.getCli_ide());
			pstm.setString(2, parCli.getCli_doc());
			pstm.setString(3, parCli.getCli_num());
			pstm.setString(4, parCli.getCli_nom());
			pstm.setString(5, parCli.getCli_dir());
			pstm.setString(6, parCli.getCli_tel());
			pstm.setString(7, parCli.getCli_email());
			pstm.setString(8, parCli.getCli_web());
			pstm.setString(9, parCli.getCli_act());
			pstm.setInt(10, parCli.getCli_ver());

			if (pstm.executeUpdate() == 1) {
				resSql = pstm.getGeneratedKeys();
				while (resSql.next()) {

					resCod = resSql.getInt(1);

				}
				resSql.close();
			}// Fin de if(pstm.executeUpdate() == 1)

			pstm.close();
			return resCod;
		} catch (SQLException e) {
			// funUsu.mostrarMensaje("¡¡¡Error en metodo agregar()", this.getClass().getName());
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Agregar ORCliente

	public int actualizar(Cliente parCli) {

		int resCod = 0;
		String conSql;
		PreparedStatement pstm;
		try {
			conSql = "update cliente set cli_ide=?,cli_doc=?,cli_num=?,cli_nom=?,cli_dir=?,cli_tel=?, " +
					"cli_email=?,cli_web=?,cli_act=?,cli_ver=? where cli_cod=? and cli_ver=?";
			pstm = obtenerConeccion().prepareStatement(conSql);

			pstm.setString(1, parCli.getCli_ide());
			pstm.setString(2, parCli.getCli_doc());
			pstm.setString(3, parCli.getCli_num());
			pstm.setString(4, parCli.getCli_nom());
			pstm.setString(5, parCli.getCli_dir());
			pstm.setString(6, parCli.getCli_tel());
			pstm.setString(7, parCli.getCli_email());
			pstm.setString(8, parCli.getCli_web());
			pstm.setString(9, parCli.getCli_act());
			pstm.setInt(10, parCli.getCli_ver() + 1);
			pstm.setInt(11, parCli.getCli_cod());
			pstm.setInt(12, parCli.getCli_ver());

			if (pstm.executeUpdate() == 1) {

				resCod = parCli.getCli_cod();

			}// Fin de if(pstm.executeUpdate() == 1)

			pstm.close();
			return resCod;
		} catch (SQLException e) {
			// funUsu.mostrarMensaje("¡¡¡Error en metodo agregar()", this.getClass().getName());
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Agregar ORCliente

	public boolean eliminar(String parCliCod) {
		String conSql;
		PreparedStatement pstm;
		try {
			conSql = "delete from cliente where per_cod=? ";
			pstm = obtenerConeccion().prepareStatement(conSql);
			pstm.setString(1, parCliCod);

			int afectadas = pstm.executeUpdate();
			pstm.close();
			return (afectadas == 1) ? true : false;

		} catch (SQLException e) {
			// funUsu.mostrarMensaje("¡¡¡Error en metodo eliminar()", this.getClass().getName());
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Agregar ORCliente

	public List<Cliente> getListaCliente(String parCliNom) {

		ResultSet resSql;
		List<Cliente> lisCli = new ArrayList<Cliente>();
		String conSql = "";
		try {

			if (parCliNom.equals("*")) {

				conSql = "select * from cliente order by cli_nom";

			} else {

				conSql = "select * from cliente where (cli_nom like '" + parCliNom + "%' or cli_nom like '%" + parCliNom + "%') order by cli_nom";

			}
			resSql = obtenerConeccion().prepareStatement(conSql).executeQuery();
			Cliente varCli;

			while (resSql.next()) {

				varCli = new Cliente();

				varCli.setCli_cod(resSql.getInt("cli_cod"));
				varCli.setCli_ide(resSql.getString("cli_ide"));
				varCli.setCli_doc(resSql.getString("cli_doc"));
				varCli.setCli_num(resSql.getString("cli_num"));
				varCli.setCli_nom(resSql.getString("cli_nom"));
				varCli.setCli_dir(resSql.getString("cli_dir"));
				varCli.setCli_tel(resSql.getString("cli_tel"));
				varCli.setCli_email(resSql.getString("cli_email"));
				varCli.setCli_web(resSql.getString("cli_web"));
				varCli.setCli_act(resSql.getString("cli_act"));
				varCli.setCli_ver(resSql.getInt("cli_ver"));

				lisCli.add(varCli);

			}// Fin de metodo
			resSql.close();

			return lisCli;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);

		}// Fin Catch en caso de que haya un error
	}// Fin de Metodo Lista de Ids

	public Cliente obtenerCliente(int parCliCod) {

		String conSql;
		PreparedStatement pstm;
		try {

			conSql = "select * from cliente where cli_cod=" + parCliCod;
			pstm = obtenerConeccion().prepareStatement(conSql);

			ResultSet resSql = pstm.executeQuery();
			cli.limpiarInstancia();

			while (resSql.next()) {

				cli.setCli_cod(resSql.getInt("cli_cod"));
				cli.setCli_ide(resSql.getString("cli_ide"));
				cli.setCli_doc(resSql.getString("cli_doc"));
				cli.setCli_num(resSql.getString("cli_num"));
				cli.setCli_nom(resSql.getString("cli_nom"));
				cli.setCli_dir(resSql.getString("cli_dir"));
				cli.setCli_tel(resSql.getString("cli_tel"));
				cli.setCli_email(resSql.getString("cli_email"));
				cli.setCli_web(resSql.getString("cli_web"));
				cli.setCli_act(resSql.getString("cli_act"));
				cli.setCli_ver(resSql.getInt("cli_ver"));

			}

			return cli;

		} catch (SQLException e) {

			e.printStackTrace();
			throw new RuntimeException(e);

		}// Fin Catch en caso de que haya un error

	}

	public Cliente leerPersonaNombre(String parCliNom) {

		String conSql;
		PreparedStatement pstm;
		try {

			conSql = "select * from cliente where cli_nom='" + parCliNom + "'";
			pstm = obtenerConeccion().prepareStatement(conSql);
			ResultSet resSql = pstm.executeQuery();
			
			cli.limpiarInstancia();

			while (resSql.next()) {

				cli.setCli_cod(resSql.getInt("cli_cod"));
				cli.setCli_ide(resSql.getString("cli_ide"));
				cli.setCli_doc(resSql.getString("cli_doc"));
				cli.setCli_num(resSql.getString("cli_num"));
				cli.setCli_nom(resSql.getString("cli_nom"));
				cli.setCli_dir(resSql.getString("cli_dir"));
				cli.setCli_tel(resSql.getString("cli_tel"));
				cli.setCli_email(resSql.getString("cli_email"));
				cli.setCli_web(resSql.getString("cli_web"));
				cli.setCli_act(resSql.getString("cli_act"));
				cli.setCli_ver(resSql.getInt("cli_ver"));

			}

			return cli;

		} catch (SQLException e) {

			e.printStackTrace();
			throw new RuntimeException(e);

		}// Fin Catch en caso de que haya un error

	}

	public boolean existeCliente(int parCliCod, String parCliIde) {

		String conSql;
		ResultSet resSql;
		try {

			if (parCliCod == 0) {

				conSql = "select * from cliente where cli_ide = '" + parCliIde + "'";

			}// Fin de if(parVenCod.equals(""))
			else {

				conSql = "select * from cliente where cli_cod <> " + parCliCod + " and cli_ide = '" + parCliIde + "'";

			}

			resSql = obtenerConeccion().prepareStatement(conSql).executeQuery();

			if (resSql.next()) {

				return true;

			}

			return false;

		} catch (SQLException e) {

			e.printStackTrace();
			throw new RuntimeException(e);

		}// Fin Catch en caso de que haya un error

	}// Fin de metodo para leer un registro de cliente

	public static void main(String[] args) {
		SwingUtilities.invokeLater(new Runnable() {

			@Override
			public void run() {
			}
		});
	}// Fin de metodo main
}// Fin de clase prinicipal
